IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetEMailTemplate]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[GetEMailTemplate]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO 
/******************************************************************  
* Name:   GetEMailTemplate  
* Purpose:  Retrieves the email template with the specified type  
*  
* PARAMETERS  
* Name         Description       
* -------------      -------------------------------------------  
*      
******************************************************************/  
  
CREATE Procedure [dbo].[GetEMailTemplate]   
@type VARCHAR(50),  
@moduleType VARCHAR(256)  
AS  
BEGIN  
  
 SET NOCOUNT ON  
  
  Select Id,  
   Type,  
   RefCode.RefMeaning as TypeMeaning,  
   BodyText,
   [Subject],
   ModuleType,
   isnull(dbo.GetServerEmail(), [To]) 'To', [From]
  FROM EMailTemplate LEFT JOIN RefCode ON EMailTemplate.Type = RefCode.RefCode  
  WHERE type = @type  
   AND ModuleType = @moduleType  
  ORDER BY TypeMeaning  
   
END  
  
  